/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.ufpb.halloc.dados;

import br.com.ufpb.halloc.conexaoDB.Pool;
import br.com.ufpb.halloc.conexaoDB.PoolIF;
import br.com.ufpb.halloc.exceptions.AtualizacaoDeTurmaException;
import br.com.ufpb.halloc.exceptions.TurmaInexistenteException;
import br.com.ufpb.halloc.exceptions.TurmaVaziaException;
import br.com.ufpb.halloc.negocio.Turma;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Rennan
 */
public class TurmaDAOPostgreSQL implements TurmaDAO{
    
    //SINGLETON
    private static TurmaDAOPostgreSQL turmaDaoPSQL;
    private PoolIF pool;
            
            
    public static TurmaDAOPostgreSQL getInstance(){
        if(turmaDaoPSQL == null)
            return new TurmaDAOPostgreSQL();
        return turmaDaoPSQL;
    }
    
    private TurmaDAOPostgreSQL (){
        this.pool = new Pool();
    }

    @Override
    public void insert(Turma turma) {
        Connection con = this.pool.getConnection();
        PreparedStatement p;
        
        String sqlInsert = "INSERT INTO turma (codturma, nometurma, nrdisciplina, nrcurso) VALUES (?, ?, ?, ?);";
        
        try {
            p = con.prepareStatement(sqlInsert);
            p.setString(1, turma.getCodTurma());
            p.setString(2, turma.getNomeTurma());
            p.setString(3, turma.getCodDisciplina());
            p.setString(4, turma.getNrCurso());
            
            p.execute();
            p.close();
        } catch (SQLException ex) {
            Logger.getLogger(TurmaDAOPostgreSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally { 
            pool.liberarConnection(con);
        }
    }

    @Override
    public void delete(Turma turma) throws TurmaInexistenteException, TurmaVaziaException {
        Connection con = this.pool.getConnection();
        PreparedStatement ps;

        String sqlDelete = "DELETE FROM turma WHERE codturma = ?;";
				
	try {
            ps = con.prepareStatement(sqlDelete);
            ps.setString(1, turma.getCodTurma());
	    ps.executeUpdate();
	    ps.close();
            
         } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
         } finally {
            pool.liberarConnection(con);
	 }
    }

    @Override
    public void update(Turma turma) throws AtualizacaoDeTurmaException {
        Connection con = this.pool.getConnection();
        PreparedStatement p;
        
        String statement = "UPDATE turma SET nometurma = ? WHERE codturma = ?;";
        
        try {
            p = con.prepareStatement(statement);
            p.setString(1, turma.getNomeTurma());
            p.setString(2, turma.getCodTurma());
            
            p.execute();
            p.close();
        } catch (SQLException ex) {Logger.getLogger(TurmaDAOPostgreSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally { pool.liberarConnection(con);}
    }

    @Override
    public Turma findID(String id) throws TurmaInexistenteException {
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;
        Turma turma = null;
        
        String sqlQuery = "SELECT * FROM turma WHERE codturma = ?;";
        
        try {
            ps = con.prepareStatement(sqlQuery);
            ps.setString(1, id);
            rs = ps.executeQuery();            
            while (rs.next()){
                    turma = new Turma(rs.getString("codturma"),
                                      rs.getString("nometurma"),
                                      rs.getString("nrdisciplina"),
                                      rs.getString("nrcurso"));
            }
            
            if (turma == null) {
                throw new TurmaInexistenteException("Esta Turma não existe!");
            }
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return turma;
    }

    
    public List<Turma> getTurma(String nome) throws TurmaInexistenteException {
        Connection conn = this.pool.getConnection();
        PreparedStatement ps;
        List<Turma> turmas = new ArrayList<Turma>();
        
        String consultaSQL = "SELECT * FROM turma WHERE LOWER(nometurma) LIKE '"+nome.toLowerCase()+"%';";
        
        try {
            ps = conn.prepareStatement(consultaSQL);
            ResultSet rset = ps.executeQuery();
            while(rset.next()){
                Turma turma = new Turma(rset.getString("codturma"),
                    rset.getString("nometurma"),
                    rset.getString("nrdisciplina"),
                    rset.getString("nrcurso"));
                turmas.add(turma);
            }
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaDAOPostgreSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(conn);
        }
        
        /* ESTAVA COM PROBLEMAS... e, para conservar o estado anterior, foi todo comentado!        
        String consultaSQL = "SELECT * FROM turma WHERE LOWER(nometurma)=nome;";
        Turma turma;
        try {
            ps = conn.prepareStatement(consultaSQL);
            ResultSet rset = ps.executeQuery();
            while (rset.next()) {
                turma = new Turma(rset.getString("codturma"),
                                  rset.getString("nometurma"),
                                  rset.getString("nrdisciplina"),
                                  rset.getString("nrcurso"));
                turmas.add(turma);
            }
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(TurmaDAOPostgreSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {this.pool.liberarConnection(conn);}        
        */
        return turmas;
    }
    
    
    @Override
    public int size() {
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;
        String sqlSelect = "SELECT COUNT(*) as total FROM turma;";
        int total = 0;
        try {
            ps = con.prepareStatement(sqlSelect);
            rs = ps.executeQuery();
            rs.next(); 
            total = rs.getInt("total");
               
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return total;
    }

    @Override
    public List getTurmas() {
        Connection con = this.pool.getConnection();
        PreparedStatement p;
        List<Turma> resultado = new ArrayList<Turma>();
        
        String statement = "SELECT * FROM turma ORDER BY nometurma;";
        try {
            p = con.prepareStatement(statement);
            
            ResultSet r = p.executeQuery();
            
            while (r.next()){
                Turma t = new Turma(r.getString("codturma"), 
                                    r.getString("nometurma"),
                                    r.getString("nrdisciplina"),
                                    r.getString("nrcurso"));
                resultado.add(t);
            }
        } catch (SQLException ex) {
            Logger.getLogger(TurmaDAOPostgreSQL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(con);
        }
        return resultado;
    }

    @Override
    public Turma findNome(String nome) throws TurmaInexistenteException {
        throw new UnsupportedOperationException("Not supported yet.");
    }


  public List<Turma> turmasAlocas(){
        
        List<Turma> retorno = new ArrayList<Turma>();
        
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;
        
        String sql = "SELECT m.coddisciplina, m.nrturma, nrcurso, nometurma, m.matprofessor FROM ministra m , turma t WHERE " +
                "m.nrturma = t.codturma";
         try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            Turma t;
            while (rs.next()) {
              t = new Turma(rs.getString("codturma"), rs.getString("nometurma"), rs.getString("nrcurso"), rs.getString("coddisciplina"));
              retorno.add(t);
            }
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return retorno;
    }
  
    public List<Turma> turmasabertas(){
        
        List<Turma> retorno = new ArrayList<Turma>();
        
        Connection con = pool.getConnection();
        PreparedStatement ps;
        ResultSet rs;
        
       
        
        String sql = "SELECT t.nrdisciplina, t.codturma, t.nrcurso, t.nometurma FROM turma t left join ministra m on t.codturma = m.nrturma" +
                " where m.nrturma is null ;";
         try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            Turma t;
            while (rs.next()) {
                t = new Turma(rs.getString("codturma"), rs.getString("nometurma"), rs.getString("nrcurso"), rs.getString("nrdisciplina"));
                retorno.add(t);
            }
            ps.close();
        } catch (SQLException ex) {
            Logger.getLogger(ProfessorDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            pool.liberarConnection(con);
        }
        return retorno;
    }
       
    
}
